package jdbcConnect;

import java.sql.*;

public class Test {
    private final String jdbcDriver = "com.mysql.cj.jdbc.Driver";
    private final String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=true&serverTimezone" +
            "=Asia/Shanghai&rewriteBatchedStatements=true";
    private final String userName = "root";
    private final String userPasswd = "root";
    private Connection connection;  //连接对象
    private Statement statement;

    private PreparedStatement preparedStatement;
    private PreparedStatement pre1;

    public Test(){
        try{
            Class.forName(jdbcDriver);
            connection = DriverManager.getConnection(url,userName,userPasswd);
            if(connection !=null&&!connection.isClosed()){
                System.out.println("与mysql建立连接成功！");
                statement = connection.createStatement();
                //开启手动提交
                connection.setAutoCommit(false);
                //设置隔离级别为可重复度级别
                connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public void close() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //    public void insertTest(int id, String name, String age,String sex) {
//        String sql = "insert student values(" + id + ",'" + name + "','"
//                + age + "','" + sex + "')";
//        System.out.println("sql: " + sql);
//        try {
//            statement.executeUpdate(sql);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }

    public PreparedStatement createInserSQL(){
        String sql = "insert student values(?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }

    public void insertTest(PreparedStatement pre,int id, String name, String age,String sex) {
        //占位符  ？
        try {
            //参数传递mysql
            pre.setString(1, String.valueOf(id));
            pre.setString(2, name);
            pre.setString(3, age);
            pre.setString(4, sex);

            pre.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertTest(int id, String name, String age,String sex){
        //占位符
        String sql = "insert student values(?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,String.valueOf(id));
            preparedStatement.setString(2,name);
            preparedStatement.setString(3,age);
            preparedStatement.setString(4,sex);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void selectTest() {
//        String sql = "select * from student where s_id < 03";
        String sql = "select * from student";
        try {
            ResultSet resultSet = statement.executeQuery(sql);//
            //ResultSet 结果集
            while (resultSet.next()) {
                System.out.println("s_id: " + resultSet.getString(1)
                        + "  s_name: " + resultSet.getString(2)
                        + "  s_age: " + resultSet.getString(3) +
                        "  s_sex: " + resultSet.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("===============================================");
        System.out.println("===============================================");
    }

    //证明会产生SQL注入
    public void login(String name, String passwd){
        String sql = "select * from user where name='"+name+"'and passwd ='" + passwd+"' or '1=1'";
        System.out.println();
        try{
            ResultSet resultSet = statement.executeQuery(sql);
            if(resultSet !=null&&resultSet.next()){
                System.out.println("注入成功！");
            }else{
                System.out.println("注入失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //SQL注入
    public void loginPre(String name, String password) {
        String sql = "select * from user where name = ? and passwd =  ? or 1=1";
        try {
            PreparedStatement pre = connection.prepareStatement(sql);
            pre.setString(1,name);
            pre.setString(2,password);

            //打印pre的内容
            System.out.println("pre: "+pre);

            ResultSet resultSet = pre.executeQuery();
            if (resultSet != null && resultSet.next()) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void tranTest() {
        String sql = "update ac_a set money = money + ? where id = ?";
        String sql1 = "update ac_b set money = money - ? where id = ?";
        Savepoint p1 = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            pre1 = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, 50);
            preparedStatement.setInt(2, 1);
            pre1.setInt(1, 50);
            pre1.setInt(2, 1);

            preparedStatement.executeUpdate();
            p1 = connection.setSavepoint("p1");
            pre1.executeUpdate();

            int i = 4 / 0;
        } catch (Exception e) {
            System.out.println("rollback 触发");
            try {
//                connection.rollback();
                if (p1 != null) {
                    connection.rollback(p1);
                }
            } catch (SQLException e1) {
                System.out.println("保存点无效！");
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }

    public void selectTest2() {
        String sql = "select * from ac_a";
        String sql1 = "select * from ac_b";
        try {
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("id: " + resultSet.getString(1)
                        + "money: " + resultSet.getString(2));
            }
            System.out.println("******************************************");
            preparedStatement = connection.prepareStatement(sql1);
            ResultSet resultSet1 = preparedStatement.executeQuery();
            while (resultSet1.next()) {
                System.out.println("id: " + resultSet1.getString(1)
                        + "money: " + resultSet1.getString(2));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        Test test = null;
        try {
            test = new Test();

            //SQL注入
            test.loginPre("小王","66");
//            test.tranTest();
//            test.selectTest2();
//            test.insertTest(10, "小红", "20", "女");

            /*PreparedStatement pre = test.createInserSQL();
            test.insertTest(pre,12, "法国红酒", "12","女");*/
//            test.selectTest();
        }finally {
            if(test!=null){
                test.close();
            }
        }
    }


}
